# LuckPay Web Manage System
#
# Copyright (c) 2016 Lucky Byte, Inc.
#
express = require 'express'
pgsql   = require 'pg'
moment  = require 'moment'
fs      = require 'fs'
mkdirp  = require 'mkdirp'
path    = require 'path'
router = express.Router()
module.exports = router


router.all '/', (req, res, next) ->
    res.locals.stat_title = '助农取款资金池变更汇总'

    # 如果没有提供日期，则仅查询当日
    res.locals.start_date = req.body.start_date or \
        req.query.start_date or moment().format('YYYY-MM-DD')
    res.locals.end_date = req.body.end_date or \
        req.query.end_date or moment().format('YYYY-MM-DD')

    where_clause = """(
        f.name like $1 or f.agent like $1
    ) and
        a.ctime::date >= '#{res.locals.start_date}' and
        a.ctime::date <= '#{res.locals.end_date}'
    """

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    # 查询资金池变动统计，按资金池分组
    await client.query \
        "select a.p2_fund,
            ( select 0 - sum(amt) from pay_p2_fund_alter
                where type = 1 and p2_fund = a.p2_fund and
                    ctime::date >= '#{res.locals.start_date}' and
                    ctime::date <= '#{res.locals.end_date}'
            ) as type_1_amt,
            ( select 0 - sum(amt) from pay_p2_fund_alter
                where type = 2 and p2_fund = a.p2_fund and
                    ctime::date >= '#{res.locals.start_date}' and
                    ctime::date <= '#{res.locals.end_date}'
            ) as type_2_amt
            from pay_p2_fund_alter as a
            left join pay_p2_fund as f on f.uuid = a.p2_fund
            where #{where_clause}
            group by a.p2_fund", [
                "%#{res.locals.keyword}%"
            ], defer err, result
    if err then done(client); return next(err)
    res.locals.records = result.rows
    res.locals.total = res.locals.records.length

    for record in res.locals.records
        await client.query \
            "select a.remains,
                f.fund_total as fund_total,
                f.fund_remains as fund_remains,
                f.name as fund_name, f.agent as fund_agent,
                f.contact as fund_contact
            from pay_p2_fund_alter as a
            left join pay_p2_fund as f on f.uuid = a.p2_fund
            where #{where_clause} and p2_fund = $2
            order by a.serial limit 1", [
                "%#{res.locals.keyword}%", record.p2_fund
            ], defer err, result
        if err then done(client); return next(err)
        record.remains = result.rows[0].remains
        record.fund_total = result.rows[0].fund_total
        record.fund_remains = result.rows[0].fund_remains
        record.fund_name = result.rows[0].fund_name
        record.fund_agent = result.rows[0].fund_agent
        record.fund_contact = result.rows[0].fund_contact

    done()
    res.render 'trstat/p2_fund_sum'
